package com.demodis.dao.impl;

import java.util.ArrayList;
import java.util.List;

import com.demodis.dao.PersDutyDaoI;
import com.demodis.domain.TblPersDuty;
import com.demodis.util.DBUtil;

public class PersDutyDaoImpl extends BaseDao implements PersDutyDaoI {

	@Override
	public boolean save(TblPersDuty t) {
		String sql = "INSERT INTO TBL_PERSDUTY(userId, PDCONT) VALUES(?, ?);";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, t.getUserId());
			ps.setString(2, t.getPdCont());

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i == 1 ? true : false;
	}

	@Override
	public boolean delete(TblPersDuty t) {
		String sql = "DELETE FROM TBL_PERSDUTY WHERE PDID IN(" + t.getIds() + ");";
		int i = 0;
		try {
			conn = DBUtil.getConnection();

			ps = conn.prepareStatement(sql);
			i = ps.executeUpdate();

		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i > 0 ? true : false;
	}

	@Override
	public boolean modify(TblPersDuty t) {
		String sql = "UPDATE TBL_PERSDUTY SET userId = ?, PDCONT = ? WHERE pdId = ?;";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			ps.setInt(1, t.getUserId());
			ps.setString(2, t.getPdCont());
			ps.setInt(3, t.getPdId());

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}

		return i == 1;
	}

	@Override
	public List<TblPersDuty> get(TblPersDuty t) {
		String sql = "SELECT a.*, b.userRealName, c.groupName FROM tbl_persduty a, tbl_user b, tbl_group c WHERE a.userId = b.userId AND c.groupId = b.groupId LIMIT ?, ?;";
		List<TblPersDuty> list = new ArrayList<TblPersDuty>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (t.getPage() - 1) * t.getRows());
			ps.setInt(2, t.getRows());
			rs = ps.executeQuery();
			while (rs.next()) {
				TblPersDuty persDuty = new TblPersDuty();
				persDuty.setPdId(rs.getInt("pdId"));
				persDuty.setUserId(rs.getInt("userId"));
				persDuty.setGroupName(rs.getString("groupName"));
				persDuty.setUserRealName(rs.getString("userRealName"));
				list.add(persDuty);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public TblPersDuty get(int userId) {
		String sql = "SELECT a.*, b.userRealName, c.groupName FROM tbl_persduty a, tbl_user b, tbl_group c WHERE a.userId = b.userId AND c.groupId = b.groupId AND A.userId = ?;";
		TblPersDuty persDuty = null;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, userId);
			rs = ps.executeQuery();
			if (rs.next()) {
				persDuty = new TblPersDuty();
				persDuty.setPdId(rs.getInt("pdId"));
				persDuty.setUserId(rs.getInt("userId"));
				persDuty.setUserRealName(rs.getString("userRealName"));
				persDuty.setPdCont(rs.getString("pdCont"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return persDuty;
	}

	@Override
	public List<TblPersDuty> get() {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<TblPersDuty> getByGroupId(TblPersDuty t) {
		String sql = "SELECT a.*, b.userRealName, c.groupName FROM tbl_persduty a, tbl_user b, tbl_group c WHERE a.userId = b.userId AND c.groupId = b.groupId AND c.groupId = ? LIMIT ?, ?;";
		List<TblPersDuty> list = new ArrayList<TblPersDuty>();
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, t.getGroupId());
			ps.setInt(2, (t.getPage() - 1) * t.getRows());
			ps.setInt(3, t.getRows());
			rs = ps.executeQuery();
			while (rs.next()) {
				TblPersDuty persDuty = new TblPersDuty();
				persDuty.setPdId(rs.getInt("pdId"));
				persDuty.setUserId(rs.getInt("userId"));
				persDuty.setGroupName(rs.getString("groupName"));
				persDuty.setUserRealName(rs.getString("userRealName"));
				list.add(persDuty);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public int getCount() {
		String sql = "SELECT COUNT(*) FROM tbl_persduty";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);

			rs = ps.executeQuery();

			if (rs.next()) {
				i = rs.getInt(1);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i;
	}

	@Override
	public int getCountByGroupId(int groupId) {
		String sql = "SELECT COUNT(*) FROM tbl_persduty a, tbl_user b, tbl_group c WHERE a.userId = b.userId AND b.groupId = c.groupId AND c.groupId = ?";
		int i = 0;
		try {
			conn = DBUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setInt(1, groupId);
			rs = ps.executeQuery();

			if (rs.next()) {
				i = rs.getInt(1);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn, ps, rs);
		}
		return i;
	}

}
